/**
 * $Id: DealOracleDBUtil.java,v 1.22 2006/10/27 08:10:56 martin Exp $
 *
 * Copyright 2006 Skyinfo, Inc. All Rights Reserved.
 */
package com.haoxi.gateway.util.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.haoxi.gateway.common.MTStatusAndReportBean;
import com.haoxi.gateway.common.SQLConst;
import com.haoxi.gateway.common.SYSConst;
import com.haoxi.gateway.runner.RunnerMgr;
import com.haoxi.gateway.runner.smscommand.SubmitCmd;
import com.haoxi.gateway.runner.smscommand.SubmitErrorMsg;

/**
 * TODO
 * 
 * Revision History
 * 
 * 2006-6-2,star.Zhao,created it
 */
public class DealOracleDBUtil {
    private static final Log log = LogFactory.getLog(DealOracleDBUtil.class);
    private static final int opCode = RunnerMgr.getInstance().getGatewayInfo()
            .getOpCode();
    private static final String SUCCESS_REPORT_ALL = "success";
    private static final String SUCCESS_REPORT_CM = "DELIVRD";
    private static final String SUCCESS_REPORT_UNICOM = "0";

    /**
     * Batch save MTmessage history to db
     * 
     * @param sendSmsCmd
     * @return
     */
    public static int[] saveBatchMsgHistory(String name, List submitCmdList,
            String dbName) {
        int[] result = null;
        if (null == submitCmdList || submitCmdList.size() <= 0) {
            return null;
        }
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DBConnectUtil.open(dbName);
            pst = conn.prepareStatement(SQLConst.SAVE_MSG_HISTORY);
            Iterator it = submitCmdList.iterator();
            while (it.hasNext()) {
                SubmitCmd submitCmd = (SubmitCmd) it.next();
                pst.setString(1, submitCmd.getMsgId());// MSGID
                pst.setInt(2, submitCmd.getStype());// Stype
                pst.setString(3, submitCmd.getServiceId());// serviceId
                pst.setString(4, submitCmd.getGateWayCode());// gatewaycode
                pst.setString(5, submitCmd.getOrgMobile());// orgmobile
                pst.setInt(6, submitCmd.getOrgMobileType());// orgmobiletype
                pst.setString(7, submitCmd.getDestMobile()); // destmobile
                pst.setInt(8, submitCmd.getDestMobileType());// destmobiletype
                pst.setString(9, submitCmd.getFeeMobile());// feemobile
                pst.setInt(10, submitCmd.getFeeMobileType());// feemobiletype
                pst.setString(11, submitCmd.getFeeType());// feetype
                pst.setInt(12, submitCmd.getFeeCode());// feecode
                pst.setString(13, submitCmd.getMsgContent());// content
                pst.setString(14, submitCmd.getSubmitStatus());// submitstatus
                pst.setInt(15, submitCmd.getProvice());// province
                pst.setInt(16, submitCmd.getCity());// citynumber
                pst.setString(17, submitCmd.getCityName());// cityname
                pst.setString(18, submitCmd.getLinkID());// reserved
                pst.setInt(19, submitCmd.getCp());// cp
                pst.setInt(20, opCode);// 运营商标示
                pst.setString(21, submitCmd.getSequence());
                String submitTime = submitCmd.getSubmitTime();
                if (submitTime.length() > 19) {
                    submitTime = submitTime.substring(0, 19);
                }
                pst.setString(22, submitTime);
                pst.setString(23, submitCmd.getMomtRelateID());
                pst.addBatch();
            }
            result = pst.executeBatch();

        } catch (SQLException sqle) {
            log.error("SQLException in " + name + ".saveMsgHistory()!", sqle);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException sqle) {
                log.error("release db connection unsuccessfully in " + name
                        + ".saveMsgHistory()!", sqle);
            }
        }

        return result;
    }

    /**
     * save MTmessage history to db
     * 
     * @param sendSmsCmd
     * @return
     */
    public static boolean saveMsgHistory(String name, SubmitCmd submitCmd,
            String dbName) {
        boolean resultDB = false;
        log.info("-----------saveMsgHistory Sequence: "
                + submitCmd.getSequence() + "--------------");
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = DBConnectUtil.open(dbName);
            pst = conn.prepareStatement(SQLConst.SAVE_MSG_HISTORY);
            pst.setString(1, submitCmd.getMsgId());// MSGID
            pst.setInt(2, submitCmd.getStype());// Stype
            pst.setString(3, submitCmd.getServiceId());// serviceId
            pst.setString(4, submitCmd.getGateWayCode());// gatewaycode
            pst.setString(5, submitCmd.getOrgMobile());// orgmobile
            pst.setInt(6, submitCmd.getOrgMobileType());// orgmobiletype
            pst.setString(7, submitCmd.getDestMobile()); // destmobile
            pst.setInt(8, submitCmd.getDestMobileType());// destmobiletype
            pst.setString(9, submitCmd.getFeeMobile());// feemobile
            pst.setInt(10, submitCmd.getFeeMobileType());// feemobiletype
            pst.setString(11, submitCmd.getFeeType());// feetype
            pst.setInt(12, submitCmd.getFeeCode());// feecode
            pst.setString(13, submitCmd.getMsgContent());// content
            pst.setString(14, submitCmd.getSubmitStatus());// submitstatus
            pst.setInt(15, submitCmd.getProvice());// province
            pst.setInt(16, submitCmd.getCity());// citynumber
            pst.setString(17, submitCmd.getCityName());// cityname
            pst.setString(18, submitCmd.getLinkID());// reserved
            pst.setInt(19, submitCmd.getCp());// cp
            pst.setInt(20, opCode);// 运营商标示
            pst.setString(21, submitCmd.getSequence());
            String submitTime = submitCmd.getSubmitTime();
            if (submitTime.length() > 19) {
                submitTime = submitTime.substring(0, 19);
            }
            pst.setString(22, submitTime);
            pst.setString(23, submitCmd.getMomtRelateID());

            int num = pst.executeUpdate();
            if (1 == num) {
                resultDB = true;
            }
        } catch (SQLException sqle) {
            log.error("SQLException in " + name + ".saveMsgHistory()!", sqle);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException sqle) {
                log.error("release db connection unsuccessfully in " + name
                        + ".saveMsgHistory()!", sqle);
            }
        }

        return resultDB;
    }

    /**
     * save history messgee status to db
     * 
     * @param sendSmsCmd
     * @return
     */
    public static boolean saveMsgHistoryStatus(String name,
            List mtStatusAndReportList, String dbName) {
        boolean resultDB = false;
        // 鏍￠獙
        if (null == mtStatusAndReportList || mtStatusAndReportList.size() == 0) {
            return false;
        }
        Connection conn = null;
        PreparedStatement pst = null;
        PreparedStatement pstForThird = null;
        int batchCount = mtStatusAndReportList.size();
        int thirdCount = 0;
        try {
            conn = DBConnectUtil.open(dbName);
            pst = conn.prepareStatement(SQLConst.SAVE_MSG_HISTORY_STATUS);
            pstForThird = conn.prepareStatement(SQLConst.SAVE_SKY3RD_STATUS);
            Iterator itr = mtStatusAndReportList.iterator();
            while (itr.hasNext()) {
                MTStatusAndReportBean mtStatusAndReport = (MTStatusAndReportBean) itr
                        .next();
                pst.setString(1, mtStatusAndReport.getMsgId());// MSGID
                pst.setInt(2, mtStatusAndReport.getStype());// Stype
                pst.setString(3, mtStatusAndReport.getServiceId());// serviceId
                pst.setString(4, mtStatusAndReport.getGateWayCode());// gatewaycode
                pst.setString(5, mtStatusAndReport.getOrgMobile());// orgmobile
                pst.setInt(6, mtStatusAndReport.getOrgMobileType());// orgmobiletype
                pst.setString(7, mtStatusAndReport.getDestMobile()); // destmobile
                pst.setInt(8, mtStatusAndReport.getDestMobileType());// destmobiletype
                pst.setString(9, mtStatusAndReport.getFeeMobile());// feemobile
                pst.setInt(10, mtStatusAndReport.getFeeMobileType());// feemobiletype
                pst.setString(11, mtStatusAndReport.getFeeType());// feetype
                pst.setInt(12, mtStatusAndReport.getFeeCode());// feecode
                pst.setString(13, mtStatusAndReport.getMsgContent());// content
                pst.setString(14, mtStatusAndReport.getSubmitStatus());// submitstatus
                pst.setInt(15, mtStatusAndReport.getProvice());// province
                pst.setInt(16, mtStatusAndReport.getCity());// citynumber
                pst.setString(17, mtStatusAndReport.getCityName());// cityname
                pst.setString(18, mtStatusAndReport.getLinkID());// reserved
                pst.setInt(19, mtStatusAndReport.getCp());// cp
                pst.setInt(20, opCode);// 杩愯惀鍟嗭紝 渚濇嵁閰嶇疆鏂囦欢
                pst.setString(21, mtStatusAndReport.getSequenceID());
                log.debug("---------------- saveMsgHistoryStatus submittime["
                        + mtStatusAndReport.getSubmitTime() + "]");
                pst.setString(22, mtStatusAndReport.getSubmitTime());
                pst.setString(23, cvtMessageReport(mtStatusAndReport
                        .getMsgReport()));
                pst.setString(24, mtStatusAndReport.getMomtRelateID());
                pst.addBatch(); // 鎵瑰鐞�
                String sid = mtStatusAndReport.getSid();
                if (null != sid && !"".equals(sid.trim())) {
                    pstForThird.setInt(1, mtStatusAndReport.getCp());
                    pstForThird.setString(2, mtStatusAndReport.getMsgId());
                    pstForThird.setString(3, cvtMessageReport(mtStatusAndReport
                            .getMsgReport()));
                    pstForThird.setString(4, sid);
                    pstForThird
                            .setString(5, mtStatusAndReport.getGateWayCode());
                    pstForThird.addBatch();
                    thirdCount++;
                }
            }
            int res[] = pst.executeBatch();
            if (null != res && res.length == batchCount) {
                resultDB = true;
            } else {
                log
                        .debug("Save msgHistoryStatus failure, stop saving the third msgHistoryStatus!");
                resultDB = false;
            }
            int res1[] = null;
            if (resultDB && thirdCount > 0) {
                res1 = pstForThird.executeBatch();
                if (null != res1 && res1.length == thirdCount) {
                    resultDB = true;
                } else {
                    resultDB = false;
                    log
                            .debug("Save msgHistoryStatus successfully, but saving the third msgHistoryStatus failure!");
                }
            }

        } catch (SQLException sqle) {
            log.error("SQLException in " + name + ".saveMsgHistoryStatus()!",
                    sqle);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException sqle) {
                log.error("release db connection unsuccessfully in " + name
                        + ".saveMsgHistoryStatus()!", sqle);
            }
        }
        return resultDB;
    }

    /**
     * @param report
     * @return
     */
    public static String cvtMessageReport(String report) {
        if (opCode == SYSConst.CM_OPCODE && report != null
                && SUCCESS_REPORT_CM.equals(report.trim())) {
            return SUCCESS_REPORT_ALL;
        }

        if (opCode == SYSConst.UNICOM_OPCODE && report != null
                && SUCCESS_REPORT_UNICOM.equals(report.trim())) {
            return SUCCESS_REPORT_ALL;
        }

        return report;
    }

    /**
     * delete dealed mt
     * 
     * @param ids
     */
    public static void deleteDealed(int[] ids, String dbName) {
        if (ids == null || ids.length <= 0) {
            return;
        }
        Connection conn = null;
        PreparedStatement ps = null;

        int tempId = 0;
        try {
            conn = DBConnectUtil.open(dbName);
            ps = conn.prepareStatement(SQLConst.DELETE_DEALED_MT_BY_ID);

            int nLen = ids.length;
            for (int i = 0; i < nLen; i++) {
                log.debug("删除的序号为："+ids[i]);
                tempId = i;
                ps.setInt(1, ids[i]);
                ps.addBatch();

            }
            int[] res = ps.executeBatch();

            if (res.length == ids.length) {
                log
                        .debug("delete submit OK! the number of dealing result are: "
                                + res.length);
            } else {
                log.error("delete submit failure !!!!!!");
            }
        } catch (SQLException e) {
            log.error("SQLException:", e);
            log.error(" the wrong id is: " + tempId);
        } catch (Exception e) {
            log.error("other Exception:", e);
            log.error(" the wrong id is: " + tempId);
        } finally {
            try {
                if (ps != null) {
                    ps.clearBatch();
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("close:", e);
            }
        }
    }

    /**
     * create error submit list
     * 
     * @param sendSmsCmd
     * @param errorCode
     * @return list
     */
    public static List createErrorSubmitList(List errorInfoList, String dbName) {
        List errorSubmitList = new ArrayList();

        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            for (int i = 0; i < errorInfoList.size(); i++) {
                String temp[] = ((String) errorInfoList.get(i)).split("&");
                int idx = Integer.parseInt(temp[0]);
                String errorCode = temp[1];

                SubmitErrorMsg submitErrorMsg = new SubmitErrorMsg();
                conn = DBConnectUtil.open(dbName);
                pst = conn.prepareStatement(SQLConst.GET_SUBMIT_BY_ID);
                pst.setInt(1, idx);
                rs = pst.executeQuery();

                // only one piece result
                while (rs.next()) {
                    submitErrorMsg.setOrgMobile(rs.getString("orgmobile"));
                    submitErrorMsg.setDestMobile(rs.getString("destmobile"));
                    submitErrorMsg.setFeeMobile(rs.getString("feemobile"));
                    submitErrorMsg.setsTyep(rs.getInt("stype"));
                    submitErrorMsg.setMtFlag(rs.getInt("mtflag"));
                    submitErrorMsg.setReportFlag(rs.getInt("reportflag"));
                    submitErrorMsg.setContent(rs.getString("content"));
                    submitErrorMsg.setGatewayCode(rs.getString("gatewaycode"));
                    submitErrorMsg.setProvince(rs.getInt("province"));
                    submitErrorMsg
                            .setProvinceName(rs.getString("provincename"));
                    submitErrorMsg.setCityNumber(rs.getInt("citynumber"));
                    submitErrorMsg.setCityName(rs.getString("cityname"));
                    submitErrorMsg.setReserved(rs.getString("reserved"));
                    submitErrorMsg.setCp(rs.getInt("cp"));
                    submitErrorMsg.setFeeMobileType(rs.getInt("feemobiletype"));
                    submitErrorMsg.setDestMobileType(rs
                            .getInt("destmobiletype"));
                    submitErrorMsg.setOrgMobileType(rs.getInt("orgmobiletype"));
                    submitErrorMsg.setIsDeal(rs.getInt("isdeal"));
                    submitErrorMsg.setReSend(rs.getInt("resend"));
                    submitErrorMsg.setTid(rs.getString("tid"));
                    submitErrorMsg.setErrorCode(errorCode);

                    errorSubmitList.add(submitErrorMsg);
                }
            }
        } catch (SQLException sqle) {
            log.error("");
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != pst) {
                    pst.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException sqle) {
                log
                        .error(
                                "release connection exception in ExceptionDateDeal.submitErrorDeal()!",
                                sqle);
            }
        }

        return errorSubmitList;
    }
}
